package com.mc.dao.Impl;

import com.mc.bean.Score;
import com.mc.dao.ScoreDao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zl
 * 成绩数据访问实现类
 */
public class ScoreDaoImpl extends BaseDao implements ScoreDao {

    @Override
    public List<Score> findByMatchCode(int matchcode) {
        List<Score> list = new ArrayList<>();
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from score where matchcode = ?";
            super.getPst(sql);
            pstmt.setInt(1,matchcode);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()){
                //创建对象
                Score score = new Score(rs.getInt(1), rs.getInt(2),rs.getInt(3));
                //放在集合中
                list.add(score);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public Score findByMatchCodeMJCode(int matchcode, int mjcode) {
        Score score=null;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from score where mjcode = ? and matchcode=?";
            super.getPst(sql);
            pstmt.setInt(1,mjcode);
            pstmt.setInt(2,matchcode);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()){
                //创建对象
                score = new Score(rs.getInt(1), rs.getInt(2),rs.getInt(3));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return score;
    }

    @Override
    public List<Score> findByMJCode(int mjcode) {
        List<Score> list = new ArrayList<>();
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "select * from score where mjcode = ?";
            super.getPst(sql);
            pstmt.setInt(1,mjcode);
            //4执行预编译指令+得到结果集
            rs = pstmt.executeQuery();
            while (rs.next()){
                //创建对象
                Score score = new Score(rs.getInt(1), rs.getInt(2),rs.getInt(3));
                //放在集合中
                list.add(score);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }
        return list;
    }

    @Override
    public int add(Score score) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "insert into score(matchcode,mjcode,score) value(?,?,?)";
            super.getPst(sql);
            pstmt.setInt(1,score.getMatchcode());
            pstmt.setInt(2,score.getMjcode());
            pstmt.setInt(3,score.getScore());
            //4执行预编译指令+得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }

        return i;
    }

    @Override
    public int update(Score score) {
        int i = 0;
        try {
            //1加载jdbc驱动
            super.regist();
            //2.链接数据库
            super.connection();
            //3创建sql语句，+预编译指令+设置参数
            String sql = "UPDATE score SET score=? WHERE matchcode=? and mjcode=?";
            super.getPst(sql);
            pstmt.setInt(1,score.getScore());
            pstmt.setInt(2,score.getMatchcode());
            pstmt.setInt(3,score.getMjcode());
            //4执行预编译指令+得到结果集
            i = pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //5关闭
            super.close();
        }

        return i;
    }
}
